import tkinter as tk
from tkinter import messagebox, StringVar

import pymssql
import pymysql
from PIL import ImageTk


# 数据库添加操作
# 商品表的添加
def add_sp():
    # 连接数据库
    connect = pymssql.connect(host="localhost", database="618", charset="utf8")
    # 创建光标
    cursor = connect.cursor()
    # 定义变量记录输入信息
    v1 = tk.StringVar()
    v2 = tk.StringVar()
    v3 = tk.StringVar()
    v4 = tk.StringVar()
    v5 = tk.StringVar()
    v6 = tk.StringVar()
    # 编写SQL语句
    sql = "insert into 商品表(商品编号,商品名称,商品单价,供应商编号,商品简介,备注) values('%s','%s','%s','%s','%s','%s')" % (v1.get(), v2.get(), v3.get(), v4.get(), v5.get(), v6.get())
    # 执行SQL语句，并且输出完成提示信息，否则回滚
    try:
        cursor.execute(sql)
        connect.commit()
        tk.messagebox.showinfo("提示", "数据添加成功")
    except:
        connect.rollback()
    # 关闭数据库连接，防止泄露
    connect.close()


# 供应商表的添加
def add_gys():
    # 连接数据库
    connect = pymssql.connect(host="localhost", database="618", charset="utf8")
    # 创建光标
    cursor = connect.cursor()
    # 定义变量记录输入信息
    v1 = tk.StringVar()
    v2 = tk.StringVar()
    v3 = tk.StringVar()
    v4 = tk.StringVar()
    v5 = tk.StringVar()
    v6 = tk.StringVar()
    v7 = tk.StringVar()
    # 编写SQL语句
    sql = "insert into 供应商表(采购明细号,采购清单号,商品编号,采购数量,商品单价,商品总价,备注) values('%s','%s','%s','%s','%s','%s','%s')" % (v1.get(), v2.get(), v3.get(), v4.get(), v5.get(), v6.get(), v7.get())
    # 执行SQL语句，并且输出完成提示信息，否则回滚
    try:
        cursor.execute(sql)
        connect.commit()
        tk.messagebox.showinfo("提示", "数据添加成功")
    except:
        connect.rollback()
    # 关闭数据库连接，防止泄露
    connect.close()


# 数据库删除操作
# 商品表的删除
def delete_sp():
    connect = pymssql.connect(host="localhost", database="618", charset="utf8")
    cursor = connect.cursor()
    sql = "delete from 商品表 where 商品编号='%s'"
    try:
        cursor.execute(sql)
        connect.commit()
        tk.messagebox.showinfo("提示", "数据删除成功")
    except:
        connect.rollback()
    connect.close()


# 数据库更新操作
# 商品表的更新
def update_sp():
    connect = pymssql.connect(host="localhost", database="618", charset="utf8")
    cursor = connect.cursor()
    sql = "update 商品表 set 商品编号 ='%s', 商品名称='%s',商品单价='%s',供应商编号='%s',商品简介='%s',备注='%s' where 商品编号='%s"
    try:
        cursor.execute(sql)
        connect.commit()
        tk.messagebox.showinfo("提示", "数据更新成功！")
    except:
        connect.rollback()
    connect.close()


# 数据库条件查询
# 商品表的查询
def select_sp():
    # 建立连接
    connect = pymysql.connect(host="localhost", user="root", password="hsb010811", database="618")
    cursor = connect.cursor()
    # 定义变量记录输入信息
    v1 = tk.StringVar()
    sql = "select * from 商品表 where 商品编号=%s"
    try:
        cursor.execute(sql, (v1.get()))
        results = cursor.fetchall()
        tk.messagebox.showinfo(title='商品表', message=results)
    except:
        return


# 检查数据库连接情况
def check_db_connection():
    try:
        connect = pymysql.connect(host="localhost", user="root", password="hsb010811", database="618")
        if connect:
            print("数据库连接成功")
            connect.close()
        else:
            print("数据库连接失败")

    except Exception as e:
        print(f"数据库连接失败: {str(e)}")


check_db_connection()


# 登录界面
def submit():
    # 创建主窗口
    root = tk.Tk()
    root.title("登录")
    width = 200
    height = 150
    screenwidth = root.winfo_screenwidth()
    screenheight = root.winfo_screenheight()
    size_geo = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 2)
    root.geometry(size_geo)

    # 创建员工号输入框
    employee_id_label = tk.Label(root, text="员工号:")
    employee_id_label.grid(row=0, column=0)
    employee_id_entry = tk.Entry(root)
    employee_id_entry.grid(row=0, column=1, pady=10)

    # 创建密码输入框
    password_label = tk.Label(root, text="密码:")
    password_label.grid(row=1, column=0)
    password_entry = tk.Entry(root, show="*")
    password_entry.grid(row=1, column=1)

    # 创建提交按钮
    submit_button = tk.Button(root, text="提交", command=submit).place(relx=0.4, rely=0.6)

    # 运行主循环
    root.mainloop()

    employee_id = employee_id_entry.get()
    password = password_entry.get()

    if (employee_id == "yg1001" and password == "00006789") or (employee_id == "yg1002" and password == "00006790") or (
            employee_id == "yg0108" and password == "00123742") or (
            employee_id == "yg0602" and password == "01080602") or (
            employee_id == "yg1080" and password == "10181234") or (employee_id == "yg4024" and password == "02403218"):
        messagebox.showinfo("登录成功", "欢迎进入超市管理系统！")
    else:
        messagebox.showerror("登录失败", "员工号或密码错误，请重试。")


# 主界面
def mainpage():
    global window
    window = tk.Tk()
    window.title("超市管理系统")
    # window.geometry('500x400')
    # 生成画布，销毁后生成新的画布实现跳转
    # page = tk.Frame(window)
    # page.pack()

    tk.Label(window, text="欢迎使用超市管理系统", font=("黑体", 20)).pack(pady=10)
    button1 = tk.Button(window, text="添加信息", command=add_sp).pack(pady=10)
    button2 = tk.Button(window, text="删除信息", command=delete_sp).pack(pady=10)
    button3 = tk.Button(window, text="修改信息", command=update_sp).pack(pady=10)
    button4 = tk.Button(window, text="信息查询", command=select_sp).pack(pady=10)
    # 插入图片背景
    image = tk.Image.open("超市.jpg").resize((500, 300))
    pyt = ImageTk.PhotoImage(image)
    canvas_window = tk.Canvas(window, width=500, height=400)
    canvas_window.create_image(250, 200, image=pyt)
    canvas_window.pack()

    window.mainloop()

# 主界面
if __name__ == '__main__':
    submit()
    mainpage()
